package com.akaovi.exp.exp11;

import com.akaovi.exp.exp11.domain.Course;
import com.akaovi.exp.exp11.domain.InfoResp;
import com.akaovi.exp.exp11.domain.Student;
import com.akaovi.exp.exp11.error.EnterException;
import com.akaovi.exp.exp11.error.ScoreException;
import com.akaovi.exp.exp11.error.SearchException;
import com.akaovi.exp.exp11.utils.DBUtils;
import com.akaovi.exp.exp11.utils.Utils;
import javafx.application.Application;
import javafx.geometry.HPos;
import javafx.geometry.Pos;
import javafx.geometry.VPos;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.layout.ColumnConstraints;
import javafx.scene.layout.GridPane;
import javafx.scene.layout.RowConstraints;
import javafx.scene.text.Font;
import javafx.stage.Stage;

import java.util.List;

public class Main extends Application {
    GridPane root = new GridPane();
    Font text = new Font(14);
    int width = 632, height = 400;

    // 声明全局控件
    Button enter;
    Button search;

    TextField textField1;
    TextField textField2;
    TextField textField3;
    TextField textField4;
    TextField textField5;
    TextField textField6;
    TextArea logBoard;

    @Override
    public void start(Stage stage) {
        // 界面初始化
        initial();

        // 标签注册
        setLabel();

        // 按钮注册
        setButton();
        setField();

        // 事件绑定
        bindEvent();

        // 初始化全局异常处理器
        Thread.currentThread().setUncaughtExceptionHandler((thread, throwable) -> {
            logBoard.setText(throwable.getMessage());
            throwable.printStackTrace();
        });

        // 调试网格
//        root.setGridLinesVisible(true);

        // 生成gui
        Scene scene = new Scene(root, width, height);
        stage.setTitle("学生成绩管理系统");
        stage.setScene(scene);
        stage.setResizable(false);
        stage.show();
    }

    public static void main(String[] args) {
        launch();
    }

    private void initial() {
        // 行
        RowConstraints rowConstraints1 = new RowConstraints(50);
        rowConstraints1.setValignment(VPos.CENTER);
        RowConstraints rowConstraints2 = new RowConstraints(300);
        root.getRowConstraints().add(rowConstraints1);
        root.getRowConstraints().add(rowConstraints1);
        root.getRowConstraints().add(rowConstraints2);

        // 列
        ColumnConstraints columnConstraints1 = new ColumnConstraints(50);
        columnConstraints1.setHalignment(HPos.CENTER);
        ColumnConstraints columnConstraints2 = new ColumnConstraints(95);
        columnConstraints2.setHalignment(HPos.CENTER);
        root.getColumnConstraints().add(columnConstraints1);
        root.getColumnConstraints().add(columnConstraints1);
        root.getColumnConstraints().add(columnConstraints2);
        root.getColumnConstraints().add(columnConstraints1);
        root.getColumnConstraints().add(columnConstraints2);
        root.getColumnConstraints().add(columnConstraints1);
        root.getColumnConstraints().add(columnConstraints2);
        root.getColumnConstraints().add(columnConstraints1);
        root.getColumnConstraints().add(columnConstraints2);
    }

    private void setLabel() {
        Label id = new Label("学号");
        id.setAlignment(Pos.CENTER);
        id.setFont(text);
        root.add(id, 1, 0);
        Label name1 = new Label("姓名");
        name1.setAlignment(Pos.CENTER);
        name1.setFont(text);
        root.add(name1, 3, 0);
        Label name2 = new Label("姓名");
        name2.setAlignment(Pos.CENTER);
        name2.setFont(text);
        root.add(name2, 1, 1);
        Label courseName1 = new Label("课程名");
        courseName1.setAlignment(Pos.CENTER);
        courseName1.setFont(text);
        root.add(courseName1, 5, 0);
        Label courseName2 = new Label("课程名");
        courseName2.setAlignment(Pos.CENTER);
        courseName2.setFont(text);
        root.add(courseName2, 3, 1);
        Label score = new Label("成绩");
        score.setAlignment(Pos.CENTER);
        score.setFont(text);
        root.add(score, 7, 0);
    }

    private void setButton() {
        enter = new Button("录入");
        root.add(enter, 0, 0);
        search = new Button("查找");
        root.add(search, 0, 1);
    }

    private void setField() {
        textField1 = new TextField();  // 学号
        root.add(textField1, 2, 0);
        textField2 = new TextField();  // 姓名
        root.add(textField2, 4, 0);
        textField3 = new TextField();  // 课程名
        root.add(textField3, 6, 0);
        textField4 = new TextField();  // 成绩
        textField4.setMaxWidth(85);
        root.add(textField4, 8, 0);

        textField5 = new TextField();  // 姓名2
        root.add(textField5, 2, 1);
        textField6 = new TextField();  // 课程名2
        root.add(textField6, 4, 1);

        logBoard = new TextArea();
        logBoard.setFont(text);
        logBoard.setMaxSize(615, 280);
        logBoard.setEditable(false);
        root.add(logBoard, 0, 2, 9, 1);
    }

    private void bindEvent() {
        // 按钮事件绑定
        enter.setOnAction(event -> {
            String id = textField1.getText();
            String name = textField2.getText();
            String courseName = textField3.getText();
            if (Utils.isAnyEmpty(id, name, courseName)) {
                throw new EnterException();
            }
            double score;
            try {
                score = Double.parseDouble(textField4.getText());
                if (score < 0 || score > 100) {
                    throw new ScoreException();
                }
            } catch (Exception e) {
                throw new ScoreException();
            }
            // 先查是否存在人，不存在则创建
            String sql1 = "select * from student where sid = ?";
            Student student = DBUtils.selectOne(Student.class, sql1, id);
            if (student == null) {
                String insert = "insert into student (sid, sname) value (?, ?)";
                if (!DBUtils.update(insert, id, name)) {
                    throw new RuntimeException("录入学生信息失败!");
                }
            }
            // 再查是否存在该课程，不存在录入失败
            String sql2 = "SELECT * FROM `course` WHERE cname LIKE ?";
            Course course = DBUtils.selectOne(Course.class, sql2, "%" + courseName + "%");
            if (course == null) {
                throw new RuntimeException("课程不存在!");
            }
            // 然后再sc表中关联成绩
            String insert2 = "insert into sc value (?, ?, ?)";
            if (!DBUtils.update(insert2, id, course.getCid(), score)) {
                throw new RuntimeException("录入学生成绩失败!");
            }
            logBoard.setText(String.format("录入成功!\nid=%s, name=%s, courseName=%s, score=%.1f\n",
                    id, name, courseName, score));
        });

        search.setOnAction(event -> {
            String sql;
            String name = textField5.getText();
            String courseName = textField6.getText();
            if (Utils.isAllEmpty(name, courseName)) {
                throw new SearchException();
            }
            List<InfoResp> infoResps;
            if (Utils.isEmpty(courseName)) {
                sql = "SELECT student.sid \"sid\", student.sname \"sname\", course.cname \"cname\", sc.score \"score\" " +
                        "from student LEFT JOIN sc ON sc.sid = student.sid " +
                        "LEFT JOIN course ON sc.cid = course.cid " +
                        "WHERE student.sname LIKE ?;";
                infoResps = DBUtils.selectAll(InfoResp.class, sql, "%" + name + "%");
            } else if (Utils.isEmpty(name)) {
                sql = "SELECT student.sid \"sid\", student.sname \"sname\", course.cname \"cname\", sc.score \"score\" " +
                        "from student LEFT JOIN sc ON sc.sid = student.sid " +
                        "LEFT JOIN course ON sc.cid = course.cid " +
                        "WHERE course.cname LIKE ?;";
                infoResps = DBUtils.selectAll(InfoResp.class, sql, courseName);
            } else {
                sql = "SELECT student.sid \"sid\", student.sname \"sname\", course.cname \"cname\", sc.score \"score\" " +
                        "from student LEFT JOIN sc ON sc.sid = student.sid " +
                        "LEFT JOIN course ON sc.cid = course.cid " +
                        "WHERE student.sname LIKE ? AND course.cname LIKE ?;";
                infoResps = DBUtils.selectAll(InfoResp.class, sql, "%" + name + "%", courseName);
            }
            // 三表联查
            StringBuilder builder = new StringBuilder(String.format("%-10s%-10s%-15s%s\n", "学号", "姓名", "课程名", "分数"));
            if (infoResps.size() == 0) {
                throw new RuntimeException("未查到内容!");
            }
            for (InfoResp infoResp : infoResps) {
                builder.append(String.format("%-10d%-10s%-20s%.1f\n", infoResp.getSid(), infoResp.getSname(), infoResp.getCname(), infoResp.getScore()));
            }
            logBoard.setText(builder.toString());
        });

        // 文本输入框事件绑定
    }

}